package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

import utils.util;
import connection.ConnectionManager;
import domain.Series;

public class JDBCSeriesDAO implements ISeriesDAO {

	Connection conn;
	
	/**
	 * 
	 * @return all series 
	 */
	@Override
	public List<Series> getAllSeries() {
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}

	/**
	 * @param state the status at the serie
	 * @return a list of series that have in common the state passed as parameter 
	 */
	@Override
	public List<Series> getAllSeriesByState(String state) {
		state=util.adaptParameterForQuery(state);
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE estado='"+state+"'");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}

	/**
	 * @param letter the initial letter of the series
	 * @return a list of series that start with the letter sent by parameter
	 */
	@Override
	public List<Series> getAllSeriesStartingWithLetter(char letter) {
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE titulo LIKE '"+letter+"%'");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}
	
	/**
	 * @param partOftitle the initial letter of the series
	 * @return a list of series that start with the letter sent by parameter
	 */
	@Override
	public List<Series> getSeriesThatContainstCharSequence(String partOftitle) {
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE titulo LIKE '%"+partOftitle+"%'");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}

	/**
	 * @param title the title for the series
	 * @return the series sent by parameter
	 */
	@Override
	public Series getSeriesByTitle(String title) {
		title=util.adaptParameterForQuery(title);
		Series series = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE v.titulo='"+title+"'");
			if(rs.next())
			{
				series = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}
	
	public Series getSeriesByIdVideo(Integer id) {
		Series serie = null;
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE v.vid='"+id+"'");
			if(rs.next())
			{
				serie = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return serie;
	}

	@Override
	public List<Series> getAllSeriesByTitleYearGenre(String title, Integer year, String genre) {
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE titulo LIKE '%"+title+"%' AND anno ='"+year+"' AND genero LIKE'%"+genre+"%'");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}

	@Override
	public List<Series> getAllSeriesByTitleYear(String title, Integer year) {
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE titulo LIKE '%"+title+"%' AND anno ='"+year+"'");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}

	@Override
	public List<Series> getAllSeriesByTitleGenre(String title, String genre) {
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE titulo LIKE '%"+title+"%' AND genero LIKE'%"+genre+"%'");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}

	@Override
	public List<Series> getAllSeriesByTitle(String title) {
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE titulo LIKE '%"+title+"%'");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}

	@Override
	public List<Series> getAllSeriesByYearGenre(Integer year, String genre) {
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE anno ='"+year+"' AND genero LIKE'%"+genre+"%'");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}

	@Override
	public List<Series> getAllSeriesByYear(Integer year) {
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE anno ='"+year+"'");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}

	@Override
	public List<Series> getAllSeriesByGenre(String genre) {
		List<Series> series = new LinkedList<Series>();
		try {
			conn = ConnectionManager.getInstance().openConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT sid,titulo,anno,duracion,descripcion,pais,genero,caratula,temporadas,capitulos,estado,video_vid FROM video v INNER JOIN series s ON v.vid=s.video_vid WHERE genero LIKE'%"+genre+"%'");
			Series aSeries = null;
			while(rs.next())
			{
				aSeries = new Series(rs.getInt(1),rs.getString(2), rs.getInt(3), rs.getInt(4), rs.getString(5), rs.getString(6), rs.getString(7), rs.getString(8),rs.getInt(9),rs.getInt(10),rs.getString(11),rs.getInt(12));
				series.add(aSeries);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return series;
	}
}
